package com.rd.project.service;

import com.rd.project.model.Gebruiker;
import com.rd.project.model.Score;
import com.rd.project.model.Vak;
import com.rd.project.model.Klas;
import com.rd.project.model.Test;
import com.rd.project.model.Student;
import com.rd.project.model.StudentKlas;

import java.io.*;

import java.util.List;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import static javax.ejb.TransactionAttributeType.REQUIRES_NEW;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;

import com.rd.project.service.PuntenService;
import com.rd.project.service.TestService;
import static com.sun.corba.se.spi.presentation.rmi.StubAdapter.request;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import org.apache.poi.openxml4j.opc.OPCPackage;

/**
 *
 * @author Stefan
 */
@Stateless
public class PuntenServiceImpl implements PuntenService {

    private int totaal = 20;
    private long idtabel = 1;
    private String klas = "";
    private String vak = "";
    private long vakId = 0;
    private long klasId = 0;
    private Vak vakobj;
    private Klas klasobj;
    private Vak nieuwVak;
    private Klas nieuwKlas;
    private Student student;

    private Score scoretoevoegen;

    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }

    private String test = "";
    private long testId = 0;
    private long studentId = 0;
    private int score = 0;
    private Test testobj;
    private Test nieuwTest;
    private Score nieuwResultaat;

    private int klasIdint = 0;

    private List<String> puntenlijst = new ArrayList<String>();

    @PersistenceContext
    private EntityManager em;
    private EntityManager em2;

    @TransactionAttribute(REQUIRES_NEW)
    public List<Score> getAlleScore() {
        Query q = em.createQuery("SELECT s FROM Score s");
        return (List<Score>) q.getResultList();
    }

    public void addScore(Score score) throws Exception {
        em.persist(score);
    }

    @TransactionAttribute(REQUIRES_NEW)
    public List<Score> getScoreFromStudentId(int id) {
        Query q = em.createQuery("SELECT s FROM Score s WHERE s.studentId = :id");
        q.setParameter("id", id);
        return (List<Score>) q.getResultList();
    }

    @TransactionAttribute(REQUIRES_NEW)
    public List<Score> getScoreFromTestId(int id) {
        Query q = em.createQuery("SELECT s FROM Score s WHERE s.testId = :id");
        q.setParameter("id", id);
        return (List<Score>) q.getResultList();
    }

    @TransactionAttribute(REQUIRES_NEW)
    public int getTestIdFromScoreId(int id) {
        Query q = em.createQuery("SELECT testId FROM Score s WHERE s.id = :id");
        q.setParameter("id", id);
        return (int) q.getSingleResult();
    }

    @TransactionAttribute(REQUIRES_NEW)
    public Test getTestFromTestId(int id) {
        Query q = em.createQuery("SELECT t FROM Test t WHERE t.id = :id");
        q.setParameter("id", id);
        return (Test) q.getSingleResult();
    }

    @TransactionAttribute(REQUIRES_NEW)
    public Test getTestFromScoreId(int scoreId) {
        return this.getTestFromTestId(this.getTestIdFromScoreId(scoreId));
    }

    @Override
    public Test getIdTest(String test) {
        //Query q = em.createQuery("SELECT * FROM Gebruiker g where g.login = " + gebruikersnaam + " And g.wachtwoord = " + password);
        //return (List<Gebruiker>) q.getResultList();
        Query q = em.createNamedQuery("Test.getIdTest");
        q.setParameter("testnaam", test);
        //q.setParameter("password", password);
        if (q.getResultList().size() == 1) {
            return (Test) q.getSingleResult();
        } else {
            return null;
        }
    }

    @Override
    public Vak getIdVak(String vak) {
        //Query q = em.createQuery("SELECT * FROM Gebruiker g where g.login = " + gebruikersnaam + " And g.wachtwoord = " + password);
        //return (List<Gebruiker>) q.getResultList();
        Query q = em.createNamedQuery("Vak.getIdVak");
        q.setParameter("vak", vak);
        //q.setParameter("password", password);
        if (q.getResultList().size() == 1) {
            return (Vak) q.getSingleResult();
        } else {
            return null;
        }
    }
    
    @Override
    public Vak getVak(int vakId) {
        Query q = em.createNamedQuery("Vak.getVak");
        q.setParameter("id", vakId);
        return (Vak) q.getSingleResult();
    }

    @Override
    public Klas getIdKlas(String klas) {
        //Query q = em.createQuery("SELECT * FROM Gebruiker g where g.login = " + gebruikersnaam + " And g.wachtwoord = " + password);
        //return (List<Gebruiker>) q.getResultList();
        Query q = em.createNamedQuery("Klas.getIdKlas");
        q.setParameter("klas", klas);
        //q.setParameter("password", password);
        if (q.getResultList().size() == 1) {
            return (Klas) q.getSingleResult();
        } else {
            return null;
        }
    }

    @Override
    public void getExcel(String path) {

        FileInputStream file = null;
        OPCPackage pkg = null;
        //C:\\Users\\Mathias\\Documents\\Test.xlsx

        try {

            file = new FileInputStream(path);
            // create a new OPC Package to obtain a workbook
            pkg = OPCPackage.open(file);

            //Create Workbook instance holding reference to .xlsx file 
            XSSFWorkbook workbook = new XSSFWorkbook(pkg);

            //Get first/desired sheet from the workbook 
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows one by one 
            Iterator<Row> rowIterator = sheet.iterator();

            //!!!!!!TE GEBRUIKEN
            if (test.equals("")) {
                XSSFRow row3 = sheet.getRow(2);
                XSSFCell cellB3 = row3.getCell((short) 1);
                test = cellB3.getStringCellValue();
                testobj = getIdTest(test);
                if (testobj != null) {
                    testId = testobj.getId();
                } else {

                    //klasId ophalen
                    if (klas.equals("")) {
                        XSSFRow row1 = sheet.getRow(0);
                        XSSFCell cellB1 = row1.getCell((short) 1);
                        klas = cellB1.getStringCellValue();
                        klasobj = getIdKlas(klas);
                        if (klasobj != null) {
                            klasId = klasobj.getId();
                        } else {
                            nieuwKlas.setNaam(klas);

                            em.getTransaction().begin();
                            em.persist(nieuwKlas);
                            em.getTransaction().commit();
                            em.flush();
                            klasId = nieuwKlas.getId();
                        }
                    }

                    //vakId ophalen
                    if (vak.equals("")) {
                        XSSFRow row2 = sheet.getRow(2);
                        XSSFCell cellB2 = row2.getCell((short) 1);
                        vak = cellB2.getStringCellValue();
                        vakobj = getIdVak(vak);
                        if (vakobj != null) {
                            vakId = vakobj.getId();
                        } else {
                            nieuwVak.setNaam(vak);
                            klasIdint = (int) klasId;
                            nieuwVak.setKlasId(klasIdint);

                            em.getTransaction().begin();
                            em.persist(nieuwVak);
                            em.getTransaction().commit();
                            em.flush();
                            vakId = nieuwVak.getId();
                        }
                    }

                    //nieuwe test maken en id ophalen
                    nieuwTest.setNaam(test);
                    nieuwTest.setTotaal(totaal);
                    nieuwTest.setVakId(vakId);

                    em.getTransaction().begin();
                    em.persist(nieuwTest);
                    em.getTransaction().commit();
                    em.flush();
                    testId = nieuwTest.getId();

                }
            }

            while (rowIterator.hasNext()) {

                Row row = rowIterator.next();
                //For each row, iterate through all the columns 

                if (row.getRowNum() <= 5) {
                    continue;// skip to read the first 7 row of file
                }

                Iterator<Cell> cellIterator = row.cellIterator();

                //while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                    studentId = (long) cell.getNumericCellValue();
                    cell = cellIterator.next();
                    cell = cellIterator.next();
                    score = (int) cell.getNumericCellValue();

                    //addScore(new Score(testId, studentId, score));
                    em2.getTransaction().begin();

                    nieuwResultaat.setTestId(testId);
                    nieuwResultaat.setStudentId(studentId);
                    nieuwResultaat.setScore(score);

                    em2.persist(nieuwResultaat);
                    em2.flush();
                    em2.getTransaction().commit();

                    //}
                }

                //}
                //System.out.println("");
            }
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Student getStudent(int studentId) {
        Query q = em.createNamedQuery("Student.getStudent");
        q.setParameter("id", studentId);
        return (Student) q.getSingleResult();
    }
    
    public Klas getKlasFromStudentId(int studentId) {
        Query q = em.createNamedQuery("StudentKlas.getKlasFromStudentId");
        q.setParameter("id", studentId);
        StudentKlas studentKlas = (StudentKlas) q.getSingleResult();       
        Query q2 = em.createNamedQuery("Klas.getKlas");
        q2.setParameter("id", studentKlas.getId().intValue());
        return (Klas) q2.getSingleResult();
    }
}
